Clase 3 SQL

Llaves primarias¶

Desde las primeras clases hablamos sobre la importancia de tener un identificador en cada tabla de nuestra base de datos. Dijimos que la mayoría de las tablas deberían tener un id como identificador pues de esta forma optimizaríamos nuestra base de datos.

Ahora, veamos propiamente a lo que hace alusión ese identificador del que hablamos.

En cada tabla de nuestra base de datos debemos poder identificar de manera única cada fila dentro de ella, lo cual hace que la búsqueda de información sea mucho más precisa y rápida. Para ello nos basaremos en una columna (o conjunto de columnas) que logren realizar esta unicidad de la que hablamos. Así, a dicha columna (o conjunto de columnas) la denominaremos como llave primaria (o primary key), donde en la mayoría de los casos (no en todos) será el identificador id al que asignaremos como llave primaria.

Por ejemplo, considerando la siguiente tabla

id_empleados	nombre	app	apm	puesto	salario	horario
50	Pedro	López	López	Vigilante	12500.5	00:00-12:00
100	Lupe	López	Ocaña	Cajero	9500.0	6:00-13:00
175	Pedro	Álvarez	Cabello	Cajero	9500.0	6:00-13:00

vemos que la columna nombre no puede ser llave primaria pues no identifica de manera única a cada fila de la tabla, pues vemos que hay dos entradas con el mismo valor Pedro dentro de esta columna. De la misma forma, las columnas app, apm, puesto, salario y horario no pueden ser llaves primarias. Vemos que claramente la columna id_empleados identifica de manera única cada fila de la tabla empleados por lo que definiremos a dicha columna como llave primaria. De hecho, si tuviéramos una columna que almace el CURP de cada persona, dicha columna podría ser también una llave primaria, de tal modo, podemos decir que dicha columna es una llave candidata. Del mismo modo, tendríamos que la columan id_empleados es también una llave candidata. En este ejemplo, cualquiera de ambas columnas es una buena opción para convertirse en llave primaria.

Cabe aclarar que considerando las columnas en conjunto nombre, app y apm podríamos tener también una llave primaria, pues es poco probable que dos personas tengan los mismos nombres y los mismo apellidos. No obstante, se elige como llave primaria a aquella que este conformada por el menor número de columnas.

Por otro lado, tenemos dos opciones para definir una llave primaria:

Opción 1: definir la llave primaria cuando estamos creando la tabla en cuestión.

Por ejemplo, si apenas estamos creando la tabla empleados y escribimos

CREATE TABLE empleados(
    id_empleados INT, 
    nombre VARCHAR,
    app VARCHAR,
    apm VARCHAR,
    puesto VARCHAR,
    salario real,
    horario VARCHAR
);

como deseamos que id_empleados sea la llave primaria entonces agregamos adicionalmente

CREATE TABLE empleados(
    id_empleados INT, 
    nombre VARCHAR,
    app VARCHAR,
    apm VARCHAR,
    puesto VARCHAR,
    salario real,
    horario VARCHAR

    -- Agregamos la definicion de la llave primaria
    primary key(id_empleados)
);

Con lo anterior garantizamos que cada valor de la columna id_empleados sea único. Así, por ejemplo, si en algún momento queremos cambiar el valor del id_empleados de algún empleado y colocamos un número que ya está registrado, entonces postgres nos arrojará un error debido a la definición de llave primaria en esta columna. De igual forma al momento de ingresar un empleado nuevo, si le asignamos un id_empleados que ya existe previamente, de igual manera postgres nos arrojará un error. Además, al ser llave primaria esta columna no permite valores nulos.

Opción 2 (recomendable)

Definiremos la llave primaria en una tabla que ya está almacenada en nuestra base de datos. Por ejemplo, dado que nosotros ya tenemos almacenada la tabla empleados en la base de datos, trabajaremos con esta opción para definir como llave primaria a la columna id_empleados. Para ello escribimos

ALTER TABLE empleados ADD CONSTRAINT pk_empleados_id_empleados
  PRIMARY KEY (id_empleados);

donde después de escribir ADD CONSTRAINT colocamos pk_empleados_id_empleados el cual es el nombre que le hemos asignado a dicha llave primaria. La convención es poner pk_ (primary key) después el nombre de la tabla pk_empleados y finalmente el nombre de la columna que será la llave primaria pk_empleados_id_empleados. Finalmente, después de escribir PRIMARY KEY entre paréntesis indicamos la columna (o columnas) que serán la llave primaria de dicha tabla.

Una vez que ya hemos ejecutado el código veremos en la parte del menú de la izquierda que la llave primaria que hemos definido aparece en la parte de constraints

Si deseáramos eliminar una llave primaria utilizaríamos la siguiente sintaxis

ALTER TABLE <<nombre tabla>> DROP CONSTRAINT <<nombre llave primaria>>;

De tal manera, si quisiéramos eliminar la llave primaria que acabamos de definir escribiríamos algo como

ALTER TABLE empleados DROP CONSTRAINT pk_empleados_id_empleados;

Se recomienda entonces escribir primero el código para la creación de las tablas, posteriormente el código para la definición de las llaves primarias (y foráneas) y finalmente se procedería a la inserción de los datos. De nuevo, es preciso recordar que es imprescindible realizar primero un proceso de diseño antes de pasar a programar nuestras bases de datos, en dicho proceso de diseño definimos cuáles columnas serán las llaves primarias y foráneas, definimos el tipo de datos de las columnas, entre otros.

Antes de ver el tema de llave foránea es preciso realizar el siguiente ejercicio:

Ejercicio¶

Definir una llave primaria en cada una de las tablas que tienes almacenadas en tu base de datos (referente al proyecto de la papelería).

LLave foránea¶

En la Tarea 1 se dejo como ejercicio realizar al menos 3 inserciones en cada tabla presente en tu base de datos (referente al proyecto de la papelería). Tal vez notaste que existe una relación entre todas las tablas (de hecho es así, todas las tablas de nuestra base de datos deben relacionarse), puesto que estamos trabajando con bases de datos relacionales (aunque no lo hayamos dicho hasta ahora). Sin entrar en detalles, se denomina bases de datos relacionales a aquellas que cumplen con el modelo relacional y donde todas las relaciones (o también conocidas como tablas) están conectadas.

Considerando las tablas empleados, ventas y productos podemos notar que

empleados está relacionada con ventas pues tenemos una columna denominada empleado en la tabla ventas.
ventas está relacionada con productos pues tenemos una columna denominada producto_vendido en la tabla ventas.

Con base en lo anterior, las tres tablas mencionadas están relacionadas entre sí.

Luego, debe parecer lógico que un producto vendido este previamente declarado en la tabla productos, pues no podemos vender un producto del cual no tenemos registro. De tal manera, implícitamente tenemos una restricción sobre la columna producto_vendido de la tabla ventas, además, nos gustaría que en dicha columna no podamos ingresar valores que no estén previamente registrados en la columna nombre_producto de la tabla productos. Para ello haremos uso de las llaves foráneas (o Foreign key) la cual se define como una restricción referencial o de integridad, y justamente funciona para colocar una limitación a la hora de ingresar valores a una columna (o columnas).

Por ende, utilizaremos una llave foránea sobre la columna producto_vendido para imponer una restricción referencial a partir de la columna nombre_producto de la tabla productos. Es decir, estaremos imponiendo que todos los valores que sean ingresados en la columna producto_vendido de la tabla ventas estén previamente registrados en la columna nombre_producto de la tabla productos.

Por ejemplo, supongamos que ingresamos los siguientes datos

INSERT INTO productos(nombre_productos, cantidad_almacen, precio) 
    VALUES('Pluma negra', 1000, 0.5);
INSERT INTO productos(nombre_productos, cantidad_almacen, precio) 
    VALUES('Cuaderno blanco', 800, 20.9);

SELECT * FROM productos;

Recordemos de clases pasada que ya habíamos ingresado algunos empleados en la tabla empleados

SELECT * FROM empleados;

y después ingresamos los siguientes datos en la tabla ventas:

INSERT INTO ventas(producto_vendido, cantidad, total, empleado) 
    VALUES('Pluma negra', 10, 5, 'Luis');
INSERT INTO ventas(producto_vendido, cantidad, total, empleado) 
    VALUES('Engrapadora', 1, 55.5, 'Zed');

-- Consultamos
SELECT * FROM ventas;

Sin problema alguno pudimos ingresar la información anterior, no obstante, hemos "vendido" un producto que no tenemos registrado en la tabla productos, lo cual representa un error grave para nuestro negocio pues no tenemos un correcto control sobre los productos que ofrecemos, lo cual puede representar pérdidas para el negocio. Por otro lado, vemos que en la columna empleado hemos colocado el nombre de un empleado, el cual está previamente registrado en la tabla empleados, así, en dicha columna estamos haciendo una correcta referencia.

Vemos que la referencia entre la tabla empleados y ventas es correcta, pero la referencia de la tabla productos y la tabla ventas no lo es. Lo anterior no podemos dejarlo "a la suerte" de quién ingresa la información, es decir, no podemos esperar que en algunas ocasiones la referencia con otras tablas sea correcta y en otros casos incorrecta. De tal manera, al definir la restricción de llave foránea en la columna producto_vendido de la tabla ventascon la columna nombre_producto de la tabla productos garantizamos que la referencia entre dichas columnas sea siempre correcta. Supongamos que ya definimos la llave foránea entre estas dos tablas, entonces, si ingresamos un producto vendido que no está previamente registrado en la tabla productos, entonces postgres nos arrojará un error referencial y no permitirá que se ingrese la información que pretendíamos ingresar. Así, también debemos definir una llave foránea entre las tablas ventas y empleados para que en la columna empleado de la tabla ventas tengamos sólo empleados previamente registrados en la tabla empleados.

De tal manera, la llave foránea se compone de dos tablas, una se conoce como tabla de referencia y la otra como tabla referenciada. En nuestro ejemplo tenemos que

Tabla referenciada	Tabla de referencia
`empleados`	`ventas`
`productos`	`ventas`

donde la llave foránea se define sobre la tabla de referencia. Entonces, para definir una llave foránea requerimos de una columna (o columnas) de la tabla de referencia y una columna (o columnas) de la tabla referenciada, donde la columna (o columnas) de la tabla referenciada debe tener la restricción de llave primaria.

Con base en lo anterior tendríamos un problema pues nosotros tenemos que

Tabla referenciada	Tabla de referencia
`empleados`: columna `nombre`	`ventas`: columna `empleado`
`productos`: columna `nombre_productos`	`ventas`: columna `producto_vendido`

las columnas de las tablas referenciadas no son llaves primarias en sus respectivas tablas, pues las llaves primarias las definimos sobre los id's. La solución del problemas sería considerar en la tabla ventas que las columnas producto_vendido y empleado reciban números enteros que hagan alusión a los id's del producto y de los empleados respectivamente.

Esto es, consideraremos algo como

id_ventas	producto_vendido	cantidad	total	empleado
1	1	10	5	1
2	1	100	50	2

donde el producto venido 1 hace referencia a aquel que tenga el id igual a 1 en la tabla productos; el empleado con id igual a 1 sabemos que es Luis, el empleado con id igual a 2 sabemos que es Zed. De esta forma conseguimos un equivalente a lo que teníamos anteriormente

pero ahora tenemos todo lo necesario para definir las llaves foráneas que queremos.

Para conseguirlo, la manera más fácil (y la cual podemos hacer pues tenemos muy pocos datos) es eliminar y crear nuevamente la tabla ventas colocando el tipo de dato correcto en las columnas producto_vendido y empleado, pues recordemos que las habíamos definido con tipo de dato VARCHAR por lo que no se podrá ingresar números enteros, en cambio, al definir dichas columnas con el tipo de dato int tendremos el problema resuelto. Para ello escribimos

-- Eliminamos la tabla ventas
DROP TABLE ventas;

-- Creamos la tabla ventas
CREATE TABLE ventas(
    id_ventas SERIAL,
    id_producto_vendido INT,
    cantidad INT,
    total INT,
    id_empleado INT,
    PRIMARY KEY(id_ventas)
);

Ahora bien, antes de ingresar información a nuestra nueva tabla ventas definiremos las llaves foráneas de las que hemos estado hablando. Antes, veamos que

Tabla referenciada	Tabla de referencia
`empleados`: columna `id_empleados`	`ventas`: columna `id_empleado`
`productos`: columna `id_productos`	`ventas`: columna `id_producto_vendido`

de donde escribimos

-- FK (llave foránea) entre la tabla ventas y empleados
ALTER TABLE ventas ADD CONSTRAINT fk_ventas_id_empleado_id_empleados
    FOREIGN KEY (id_empleado) REFERENCES empleados(id_empleados);

-- FK entre la tabla ventas y productos
ALTER TABLE ventas ADD CONSTRAINT fk_ventas_id_producto_vendido_id_productos
    FOREIGN KEY (id_producto_vendido) REFERENCES productos(id_productos);

donde la sintaxis general es

ALTER TABLE <<tabla referencia>> ADD CONSTRAINT fk_<<columna referencia>>_<<columna referenciada>> 
FOREING KEY (<<columna referencia>>) REFERENCES <<tabla referenciada>>(<<columna referenciada>>);

Vemos que las llaves foráneas aparecen en el menú de la izquierda en la parte de constraints

Curso PostgreSQL¶

Llaves primarias¶

Ejercicio¶

LLave foránea¶

Ejercicio¶